/**
 * 
 */
package org.gov.vis.data.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.gov.vis.data.dto.DistributionLnDto;
import org.gov.vis.util.DBConstants;
import org.gov.vis.util.DBStatements;
import org.gov.vis.util.DU;
import org.gov.vis.util.Util;

/**
 * @author user
 *
 */
public class DistributionLnDaoImpl implements DistributionLnDao{

	/**
	 *Method called to either Add or Edit a DistributionLn
	 */
	@Override
	public void addOrEditDistributionLn(final DistributionLnDto _distributionLnDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
			conn = Util.getConnection();
			if(_distributionLnDto.getDisLnId() > 0){
				pStmt = conn.prepareStatement(DBStatements.EDIT_DISLN);
				pStmt.setString(1, _distributionLnDto.getCode());
				pStmt.setString(2, DU.format(_distributionLnDto.getDoDstrbut(), DBConstants.MYSQL_DATE_FORMAT));
				
				if(_distributionLnDto.getKgs()!= null && !_distributionLnDto.getKgs().equals("")){
					pStmt.setDouble(3, Double.valueOf(_distributionLnDto.getKgs()));
				}
				else{
					pStmt.setString(3, null);
				}
				if(_distributionLnDto.getCnvtdLtrs()!= null && !_distributionLnDto.getCnvtdLtrs().equals("")){
				pStmt.setDouble(4, Double.valueOf(_distributionLnDto.getCnvtdLtrs()));
				}else{
					pStmt.setString(4, null);
				}
				pStmt.setString(5, _distributionLnDto.getFrmStation());
				pStmt.setString(6, _distributionLnDto.getVehicleNo());
				pStmt.setString(7, _distributionLnDto.getEvopLoss());
				pStmt.setLong(8, _distributionLnDto.getDisLnId());
			}
			else{
				pStmt = conn.prepareStatement(DBStatements.ADD_DISLN);
				pStmt.setString(1, _distributionLnDto.getCode());
				pStmt.setString(2, DU.format(_distributionLnDto.getDoDstrbut(), DBConstants.MYSQL_DATE_FORMAT));
				if(_distributionLnDto.getKgs()!= null && !_distributionLnDto.getKgs().equals("")){
					pStmt.setDouble(3, Double.valueOf(_distributionLnDto.getKgs()));
				}
				else{
					pStmt.setString(3, null);
				}
				if(_distributionLnDto.getCnvtdLtrs()!= null && !_distributionLnDto.getCnvtdLtrs().equals("")){
				pStmt.setDouble(4, Double.valueOf(_distributionLnDto.getCnvtdLtrs()));
				}else{
					pStmt.setString(4, null);
				}
				pStmt.setString(5, _distributionLnDto.getFrmStation());
				pStmt.setString(6, _distributionLnDto.getVehicleNo());
				pStmt.setString(7, _distributionLnDto.getEvopLoss());
				
			}

			pStmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}
	}//end addOrEditDept

	/**
	 *Method called to Delete a DistributionLn
	 */
	@Override
	public void deleteDistributionLn(final long _distributionLnId) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.DELETE_DISLN);
			pStmt.setLong(1, _distributionLnId);
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

	}//end deleteDept

	/**
	 *Method called to get the list of DistributionLns based on the passed DistributionLn 
	 */
	@Override
	public List<DistributionLnDto> getDistributionLns(final DistributionLnDto _distributionLnDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<DistributionLnDto> _distributionLnList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_DISLN);
			if(_distributionLnDto.getDisLnId() > 0){
				pStmt.setLong(1, _distributionLnDto.getDisLnId());
				pStmt.setLong(2, _distributionLnDto.getDisLnId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}
				
			if(_distributionLnDto.getCode() != null && !_distributionLnDto.getCode().trim().equals("")){
				pStmt.setString(3, _distributionLnDto.getCode());
			}
			else{
				pStmt.setString(3, "%");
			}
			if(_distributionLnDto.getFrmStation() != null && !_distributionLnDto.getFrmStation().trim().equals("")){
				pStmt.setString(4, _distributionLnDto.getFrmStation());
			}
			else{
				pStmt.setString(4, "%");
			}
			if(_distributionLnDto.getVehicleNo() != null && !_distributionLnDto.getVehicleNo().trim().equals("")){
				pStmt.setString(5, _distributionLnDto.getVehicleNo());
			}
			else{
				pStmt.setString(5, "%");
			}
			rsSet = pStmt.executeQuery();
			
			_distributionLnList = populateDistributionLns(rsSet);
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _distributionLnList;
	}//end getDistributionLns


	/**
	 * Method called to populate the DistributionLn records based on the passed result set
	 * @param _rsSet
	 * @return
	 */
	private List<DistributionLnDto> populateDistributionLns(final ResultSet _rsSet)throws Exception{
		List<DistributionLnDto> _distributionLnList = null;
		DistributionLnDto _distributionLnDto = null;
		
		while(_rsSet.next()){
			if(_rsSet.isFirst()){
				_distributionLnList = new ArrayList<DistributionLnDto>();
			}
			//initialize the DistributionLn
			_distributionLnDto = new DistributionLnDto();
			_distributionLnDto.setCode(_rsSet.getString(DBConstants.COL_DISTRIBUTION_LN_CODE));
			_distributionLnDto.setCnvtdLtrs(_rsSet.getString(DBConstants.COL_DISTRIBUTON_LN_CNVRTDLTRS));
			_distributionLnDto.setDisLnId(_rsSet.getLong(DBConstants.COL_DISTRIBUTON_LN_ID));
			_distributionLnDto.setDoDstrbut(_rsSet.getDate(DBConstants.COL_DISTRIBUTON_LN_DODSTRB));
			_distributionLnDto.setEvopLoss(_rsSet.getString(DBConstants.COL_DISTRIBUTON_LN_EVOPOSS));
			_distributionLnDto.setFrmStation(_rsSet.getString(DBConstants.COL_DISTRIBUTON_LN_FRMSTATON));
			_distributionLnDto.setKgs(_rsSet.getString(DBConstants.COL_DISTRIBUTON_LN_KGS));
			_distributionLnDto.setVehicleNo(_rsSet.getString(DBConstants.COL_DISTRIBUTON_LN_VEHICLENO));
			
			//add the current Object to the list
			_distributionLnList.add(_distributionLnDto);
			
		}
		return _distributionLnList;
	}//end populateDistributionLns

	/**
	 * Method is called to search the DistributionLns based on the distributionLn.
	 */
	@Override
	public List<DistributionLnDto> searchDistributionLns(final DistributionLnDto _distributionLnDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<DistributionLnDto> _distributionLnList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_DISLN);
			if(_distributionLnDto.getDisLnId() > 0){
				pStmt.setLong(1, _distributionLnDto.getDisLnId());
				pStmt.setLong(2, _distributionLnDto.getDisLnId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}
			if(_distributionLnDto.getCode() != null && !_distributionLnDto.getCode().trim().equals("")){
				pStmt.setString(3, _distributionLnDto.getCode() + "%");
			}
			else{
				pStmt.setString(3, "%");
			}
			if(_distributionLnDto.getFrmStation() != null && !_distributionLnDto.getFrmStation().trim().equals("")){
				pStmt.setString(4, _distributionLnDto.getFrmStation() + "%");
			}
			else{
				pStmt.setString(4, "%");
			}
			if(_distributionLnDto.getVehicleNo() != null && !_distributionLnDto.getVehicleNo().trim().equals("")){
				pStmt.setString(5, _distributionLnDto.getVehicleNo()+ "%" );
			}
			else{
				pStmt.setString(5, "%");
			}
			
			rsSet = pStmt.executeQuery();
			
			_distributionLnList = populateDistributionLns(rsSet);
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _distributionLnList;
	}//end searchDistributionLns

}// end DistributionLnDaoImpl
